﻿// MIT License
//
// Copyright (c) 2021-present zuohuaijun, Daming Co.,Ltd and Contributors
//
// 电话/微信：18020030720 QQ群1：87333204 QQ群2：252381476

using Admin.NET.Application.Const;
using Admin.Net.InformationPlatform.Entity;
using Furion.DependencyInjection;
using Furion.DynamicApiController;
using Microsoft.AspNetCore.Mvc;
using Nest;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Admin.Net.InformationPlatform.Entity.Equipment;
using Admin.Net.InformationPlatform.Service.Equipment.Dto;
using Admin.Net.InformationPlatform.Entity.ProdectFile;
using Admin.NET.Core;
using Furion.FriendlyException;
using MathNet.Numerics.Distributions;
using Admin.Net.InformationPlatform.Service.Equipment_Para.Dto;
using Microsoft.AspNetCore.Authorization;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using SharpCompress.Common;
using NPOI.HPSF;
using System.Net;
using System.Data;
using Furion;
using System.Linq.Dynamic.Core;

namespace Admin.Net.InformationPlatform.Service.Equipment;

/// <summary>
/// 华为验厂-设备管理系统
/// </summary>
[ApiDescriptionSettings(groups: "HWYC", Name = "设备管理系统", Tag = "设备管理", Order = 200)]
public class EqipmentService : IDynamicApiController, ITransient
{
    /// <summary>
    /// 单例数据链接
    /// </summary>
    ISqlSugarClient db;
    /// <summary>
    /// T100数据库
    /// </summary>
    private SqlSugarProvider _T100Db;
    private SqlSugarProvider _SjDb;
    private SqlSugarProvider _plm;
    private SqlSugarProvider _Dilon;


    /// <summary>
    /// 用户信息
    /// </summary>
    TokenUser _User;
    /// <summary>
    /// SOP工程文件管理
    /// </summary>
    /// <param name="db"></param>
    public EqipmentService(ISqlSugarClient db)
    {
        this.db = db;
        this._T100Db = this.db.AsTenant().GetConnection(T100TestArea.ConfigId);
        this._SjDb = this.db.AsTenant().GetConnection(SJDB.ConfigId);
        this._plm = this.db.AsTenant().GetConnection(PLM.ConfigId);
        this._Dilon = this.db.AsTenant().GetConnection(Dilon.ConfigId);
        // 获取 `Jwt` 存储的信息 企业编号与据点信息
        _User = Util.SjCommonUtil.GetT100UserTokenInfo(_T100Db, _Dilon);

    }

    /// <summary>
    /// 查询设备详细信息
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/HWYC/SelectEquipmentBasicInformationList")]
    public async Task<SqlSugarPagedList<HWEquipmentBasicInformationOut>> SelectEquipmentBasicInformation(SelectEquipmentBasicInput input) {


        var output = await _SjDb.Queryable<HWEquipmentBasicInformation>()
        .Where(l => l.LIFECYCLE == 0)
        .WhereIF(!string.IsNullOrEmpty(input.DEVICENUMBER), (l) => (l.DEVICENUMBER.Contains(input.DEVICENUMBER)))//设备编号
        .WhereIF(!string.IsNullOrEmpty(input.EQUIPMENTTYPENO), (l) => (l.EQUIPMENTTYPENO.Contains( input.EQUIPMENTTYPENO)))//设备类别编号
        .WhereIF(input.ARCHIVESTATE != null && input.ARCHIVESTATE != -1 && input.ARCHIVESTATE != 0, (l) => (l.ARCHIVESTATE == input.ARCHIVESTATE))//设备状态
         .WhereIF(input.ARCHIVESTATE ==0, (l) => (l.ARCHIVESTATE == input.ARCHIVESTATE || l.ARCHIVESTATE ==null))//设备状态
         .WhereIF(input.EQUIPMENTSTATE != null , (l) => (l.EQUIPMENTSTATE == input.EQUIPMENTSTATE))//设备状态 
        .OrderBy(l => l.CREATETIME, OrderByType.Desc) //创建日期排序
        .Select(l => new HWEquipmentBasicInformationOut
        {
            BID = l.BID,
            EQUIPMENTTYPENO = l.EQUIPMENTTYPENO,
            DEVICENUMBER = l.DEVICENUMBER,
            EQUIPMENTSTATE = l.EQUIPMENTSTATE ==null?0:Convert.ToInt32(l.EQUIPMENTSTATE),
            CREATETIME = l.CREATETIME,
            MAINTENANCEINTERVALS = l.MAINTENANCEINTERVALS,
            ARCHIVESTATE = l.ARCHIVESTATE ==null?0:Convert.ToInt32(l.ARCHIVESTATE),
            LOCATION = l.LOCATION,
            EQUIPMENTADMIN = l.EQUIPMENTADMIN,

        }).ToPagedListAsync(input.Page, input.PageSize);


        List<HWEquipmentBasicInformationOut> listOut = (List<HWEquipmentBasicInformationOut>)output.Items;
        if (listOut.Count == 0) {
            throw Oops.Oh($"未查询到数据！");

        }
        HashSet<string> strings = new HashSet<string>();

        foreach (HWEquipmentBasicInformationOut item in listOut)
        {
            strings.Add(item.DEVICENUMBER);
        }


      /*  var outputZi = _SjDb.Queryable<HWEquipmentBasicZi>().Where(it => strings.Contains(it.DEVICENUMBER))

            .ToList();*/
        
       List<HWMaintenanceTime> a = _SjDb.Ado.SqlQuery<HWMaintenanceTime>($@"select b.*,l.* ,(CASE WHEN MAINTENANCE_TIME is not null  THEN MAINTENANCE_TIME 
        ELSE create_time END) AS newtime,to_date(to_char((CASE WHEN MAINTENANCE_TIME is not null  THEN MAINTENANCE_TIME 
        ELSE create_time END)+maintenance_intervals,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS') As nexttime from hw_equipment_basic_information b
         left join (    SELECT *
        FROM (
          SELECT DEVICE_NUMBER, MAINTENANCE_TIME, ROW_NUMBER() OVER (PARTITION BY DEVICE_NUMBER ORDER BY MAINTENANCE_TIME DESC) AS rn
          FROM HW_MAINTENANCE_MESSAGE
        ) subquery
        WHERE rn = 1) l on b.device_number = l.DEVICE_NUMBER
        where b.life_cycle=0 ")
            .ToList();


        for (int i = 0; i < listOut.Count; i++)
        {
            /*listOut[i].MATERIALNO = new List<MaterialnoMessage>();
            foreach (HWEquipmentBasicZi item in outputZi)
            {
                if (listOut[i].DEVICENUMBER.Equals(item.DEVICENUMBER)) {

                    listOut[i].MATERIALNO.Add(new MaterialnoMessage() {

                        MATERIALNO = item.MATERIALNO,
                        ITEMNAME = item.ITEMNAME,
                        EQUIPMENTSPECS = item.EQUIPMENTSPECS,
                        DEVICENUMBER = item.DEVICENUMBER,

                    });
                }

            }*/


            foreach (HWMaintenanceTime item in a)
            {
                if (item.DEVICE_NUMBER.Equals(listOut[i].DEVICENUMBER)) {
                    listOut[i].NextRepairTime = item.NEXTTIME;
                    listOut[i].RecentRepairTime = item.NEWTIME;
                }


            }


        }
        output.Items = listOut;

        return output;

    }

    /// <summary>
    /// 添加设备信息
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/HWYC/SaveEquipmentBasicInformation")]
    public async Task<string> SaveEquipmentBasicInformation([FromBody] List<EquipmentBasicInformationInput> input)
    {
        List<HWEquipmentBasicInformation> list = new List<HWEquipmentBasicInformation>();
     //   List<HWEquipmentBasicZi> Zilist = new List<HWEquipmentBasicZi>();
        if (input.Count() > 0)
        {
            DateTime creteTime = DateTime.Now;

            for (int i = 0; i < input.Count(); i++)
            {

                if (string.IsNullOrEmpty(input[i].DEVICENUMBER) || string.IsNullOrEmpty(input[i].EQUIPMENTTYPENO) || string.IsNullOrEmpty(input[i].DEVICENUMBER))
                {
                    throw Oops.Oh($"请填写完整参数！");

                }

                //判断单据存在
                int cnt = await _SjDb.Queryable<HWEquipmentBasicInformation>()
                    .Where(a => a.DEVICENUMBER == input[i].DEVICENUMBER)
                     .Where(l => l.LIFECYCLE == 0)
                    .Select(a => a.BID)
                    .CountAsync();
                if (cnt > 0)
                {
                    throw Oops.Oh($"任务已经存在！");
                }


              /*  for (int j = 0; j < input[i].MATERIALNO.Count; j++)
                {
                    HWEquipmentBasicZi basicZi = new HWEquipmentBasicZi();
                    basicZi.MATERIALNO = input[i].MATERIALNO[j].MATERIALNO;
                    basicZi.ITEMNAME = input[i].MATERIALNO[j].ITEMNAME;
                    basicZi.DEVICENUMBER = input[i].DEVICENUMBER;
                    basicZi.EQUIPMENTSPECS = input[i].MATERIALNO[j].EQUIPMENTSPECS;
                    basicZi.CREATETIME = creteTime;
                    Zilist.Add(basicZi);
                }*/


                var details = new HWEquipmentBasicInformation
                {
                    EQUIPMENTTYPENO = input[i].EQUIPMENTTYPENO,
                    DEVICENUMBER = input[i].DEVICENUMBER,
                    EQUIPMENTSTATE = input[i].EQUIPMENTSTATE,
                    LIFECYCLE = 0,
                    CREATETIME = creteTime,
                    MAINTENANCEINTERVALS = input[i].MAINTENANCEINTERVALS,
                    ARCHIVESTATE = input[i].ARCHIVESTATE,
                    LOCATION = input[i].LOCATION,
                    EQUIPMENTADMIN = input[i].EQUIPMENTADMIN,
                };
                list.Add(details);

            }
        }
        #region 插入事务
        try
        {
            //开启事务
            _SjDb.Ado.BeginTran();
            int cnt = await _SjDb.Insertable<HWEquipmentBasicInformation>(list).ExecuteCommandAsync();
           // int cnt2 = await _SjDb.Insertable<HWEquipmentBasicZi>(Zilist).ExecuteCommandAsync();

            if (cnt == 0 )
            {
                throw Oops.Oh($"新建任务失败！");
            }
            //提交事务
            _SjDb.Ado.CommitTran();
            return "插入成功";
        }
        catch (Exception e)
        {
            //回滚事务
            _SjDb.Ado.RollbackTran();
            throw Oops.Oh($"异常：{e.Message}");
        }
        #endregion

    }

    /// <summary>
    /// 删除订单
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/HWYC/DeleteEquipmentBasicInformation")]
    public async Task<string> DeleteEquipmentBasicInformation(DeleteEquipmentInput input) {

        //判断单据存在

        /*       int selectCount = _SjDb.Queryable<HWEquipmentBasicInformation>()
                   .Where(a => a.BID == input.BID)
                   .Count();*/

        List<HWEquipmentBasicInformation> list = _SjDb.Queryable<HWEquipmentBasicInformation>()
           .Where(a => a.BID == input.BID).ToList();

        if (list.Count == 0) {

            throw Oops.Oh("任务不存在，驳回！");
        }


        else
        {
            //提交
            try
            {
                _SjDb.Ado.BeginTran();
                int count = await _SjDb.Updateable<HWEquipmentBasicInformation>()
                    .SetColumns(a => a.LIFECYCLE == 1)
                    .Where(a => a.BID == input.BID)
                    .ExecuteCommandAsync();


                int deleteCount = _SjDb.Deleteable<HWEquipmentBasicZi>().Where(it => it.DEVICENUMBER == list[0].DEVICENUMBER).ExecuteCommand();
                if (count != 1)
                {
                    throw Oops.Oh("删除失败！");
                }
                _SjDb.Ado.CommitTran();
                return "删除成功！";
            }
            catch (Exception e)
            {
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }
        }

    }


    /// <summary>
    /// 更改设备状态
    /// </summary>
    /// <returns></returns>
    [HttpPost("/HWYC/UpdateEquipmentState")]
    public async Task<string> UpdateEquipmentState(UpdateEquipmentStateInput input) {

        int selectCount = _SjDb.Queryable<HWEquipmentBasicInformation>()
                .Where(a => a.BID == input.BID)
                .Count();

        if (selectCount == 0)
        {
            throw Oops.Oh("任务不存在，驳回！");
        }
        else
        {
            //提交
            try
            {
                _SjDb.Ado.BeginTran();
                int count = await _SjDb.Updateable<HWEquipmentBasicInformation>()
                    .SetColumns(a => a.EQUIPMENTSTATE == input.EQUIPMENTSTATE)
                    .Where(a => a.BID == input.BID)
                    .ExecuteCommandAsync();

                if (count != 1)
                {
                    throw Oops.Oh("修改失败！");
                }
                _SjDb.Ado.CommitTran();
                return "修改成功！";
            }
            catch (Exception e)
            {
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }
        }




    }

    /// <summary>
    /// 更改设备信息
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/HWYC/UpdateEquipmentBasicInformation")]
    public async Task<string> UpdateEquipmentBasicInformation(UpdateEquipmentBasicInformationInput input)
    {

        if ( string.IsNullOrEmpty(input.EQUIPMENTTYPENO) || string.IsNullOrEmpty(input.DEVICENUMBER))
        {
            throw Oops.Oh($"请填写完整参数！");
        }
        DateTime dateTime = DateTime.Now;

        //判断单据存在
        int cnt = await _SjDb.Queryable<HWEquipmentBasicInformation>()
            .Where(a => a.DEVICENUMBER == input.DEVICENUMBER)
             .Where(l => l.LIFECYCLE == 0)
            .Select(a => a.BID)
            .CountAsync();
        if (cnt == 0)
        {
            throw Oops.Oh($"任务不存在！");
        }
        else
        {
            //提交
            try
            {
                _SjDb.Ado.BeginTran();
                int count = await _SjDb.Updateable<HWEquipmentBasicInformation>()
                     .SetColumns(a => a.EQUIPMENTTYPENO == input.EQUIPMENTTYPENO)
                     .SetColumns(a => a.DEVICENUMBER == input.DEVICENUMBER)
                     .SetColumns(a => a.MAINTENANCEINTERVALS == input.MAINTENANCEINTERVALS)
                     .SetColumns(a => a.LOCATION == input.LOCATION)
                     .SetColumns(a => a.EQUIPMENTADMIN == input.EQUIPMENTADMIN)
                     .Where(a => a.DEVICENUMBER == input.DEVICENUMBER)
                    .ExecuteCommandAsync();


               // int deleteCount = _SjDb.Deleteable<HWEquipmentBasicZi>().Where(it => it.DEVICENUMBER == input.DEVICENUMBER).ExecuteCommand();

              /*  List<HWEquipmentBasicZi> list = new List<HWEquipmentBasicZi>();
                foreach (MaterialnoMessage item in input.MATERIALNO)
                {
                    list.Add(new HWEquipmentBasicZi() {
                        MATERIALNO = item.MATERIALNO,
                        DEVICENUMBER = input.DEVICENUMBER,
                        ITEMNAME = item.ITEMNAME,
                        EQUIPMENTSPECS = item.EQUIPMENTSPECS,
                        CREATETIME = dateTime,


                    });


                }
                int cnt2 = await _SjDb.Insertable<HWEquipmentBasicZi>(list).ExecuteCommandAsync();*/

                if (count != 1 )
                {
                    throw Oops.Oh("修改失败！");
                }
                _SjDb.Ado.CommitTran();
                return "修改成功！";
            }
            catch (Exception e)
            {
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }

        }

    }

    /// <summary>
    /// 更改维修状态
    /// </summary>
    /// <returns></returns>
    [HttpPost("/HWYC/UpdateHWEquipmentArchivestate")]
    public async Task<string> UpdateHWEquipmentArchivestate(UpdateHWEquipmentArchivestateInput input) {

        int selectCount = _SjDb.Queryable<HWEquipmentBasicInformation>()
                  .Where(a => a.DEVICENUMBER == input.DEVICENUMBER)
                  .Count();

        if (selectCount == 0)
        {
            throw Oops.Oh("任务不存在，驳回！");
        }
        else
        {
            //提交
            try
            {
                _SjDb.Ado.BeginTran();
                int count = await _SjDb.Updateable<HWEquipmentBasicInformation>()
                    .SetColumns(a => a.ARCHIVESTATE == input.ARCHIVESTATE)
                    .Where(a => a.DEVICENUMBER == input.DEVICENUMBER)
                    .ExecuteCommandAsync();

                if (count != 1)
                {
                    throw Oops.Oh("修改失败！");
                }
                _SjDb.Ado.CommitTran();
                return "修改成功！";
            }
            catch (Exception e)
            {
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }
        }
    }

    /// <summary>
    /// 设备保养预警看板（分页） 非token认证
    /// </summary>
    /// <param name="input"></param>
    /// <returns></returns>
    [HttpPost("/HWYC/GetSpecificInformationForewarningNoHaveToken")]
    [AllowAnonymous]
    public async Task<SqlSugarPagedList<HWSpecificInformationYJ>> GetSpecificInformationForewarning(SelectEquipmentBasicInput input)
    {
        string sql = "";

        if (!string.IsNullOrEmpty(input.DEVICENUMBER))
            sql += $@" and b.DEVICE_NUMBER like '%{input.DEVICENUMBER}%'";
        return _SjDb.Ado.SqlQuery<HWSpecificInformationYJ>($@"
                 select b.*,l.* ,(CASE WHEN MAINTENANCE_TIME is not null  THEN MAINTENANCE_TIME 
                ELSE create_time END) AS newtime,to_date(to_char((CASE WHEN MAINTENANCE_TIME is not null  THEN MAINTENANCE_TIME 
                ELSE create_time END)+maintenance_intervals,'yyyy-mm-dd HH24:MI:SS'),'yyyy-mm-dd HH24:MI:SS') As nexttime from hw_equipment_basic_information b
                 left join (    SELECT *
                FROM (
                  SELECT DEVICE_NUMBER, MAINTENANCE_TIME, ROW_NUMBER() OVER (PARTITION BY DEVICE_NUMBER ORDER BY MAINTENANCE_TIME DESC) AS rn
                  FROM HW_MAINTENANCE_MESSAGE
                ) subquery
                WHERE rn = 1) l on b.device_number = l.DEVICE_NUMBER
                where b.life_cycle=0 {sql}
                ").ToPagedList(input.Page, input.PageSize);


    }

    [HttpPost("/HWYC/ExcelInput")]
    [AllowAnonymous]
    public async void ExcelInput() {
        DateTime date = DateTime.Now;

        #region 导入excel
                IWorkbook wk = null;

                string url = App.WebHostEnvironment.WebRootPath + "\\EnvironmentExcel"+ "\\2022年生产工单量汇总表(1).xlsx";
                //string url = @"E:\excel\2022年生产工单量汇总表(1).xlsx";
                string extension = Path.GetExtension(url);
                try
                {
                    FileStream fs = File.OpenRead(url);
                    if (extension.Equals(".xls"))
                    {
                        //把xls文件中的数据写入wk中
                        wk = new HSSFWorkbook(fs);
                    }
                    else
                    {
                        //把xlsx文件中的数据写入wk中
                        wk = new XSSFWorkbook(fs);
                    }

                    fs.Close();
                    //读取当前表数据
                    ISheet sheet = wk.GetSheetAt(0);

                    IRow row = sheet.GetRow(0);  //读取当前行数据

                   sheet = wk.GetSheet("Sheet1 (2)");

            List<EquipmentExcel> list = new List<EquipmentExcel>();

                    for (int i = 0; i <= sheet.LastRowNum; i++)  //LastRowNum 是当前表的总行数-1（注意）
                    {
                        row = sheet.GetRow(i);  //读取当前行数据
                        if (row != null)
                        {
                            EquipmentExcel excel = new EquipmentExcel();
                            //LastCellNum 是当前行的总列数  row.LastCellNum
                            for (int j = 0; j < 22; j++)
                            {

                                if (i>0) {

                                    switch (j) {

                                        case 0:
                                            excel.MATERIALNO = row.GetCell(j).ToString();
                                            break;

                                        case 1:
                                            excel.ITEMNAME = row.GetCell(j).ToString();
                                            break;

                                        case 2:
                                            excel.EQUIPMENTSPECS = row.GetCell(j).ToString();
                                            break;

                                        case 14:
                                            excel.EQUIPMENTTYPENO = row.GetCell(j).ToString();
                                            break;

                                        case 15:
                                            excel.DEVICENUMBER = row.GetCell(j).ToString();
                                            break;

                                        case 16:
                                            excel.CanShu = row.GetCell(j).ToString();
                                            break;

                                        case 17:
                                            excel.Min = row.GetCell(j).ToString();
                                            break;

                                        case 18:
                                            excel.Max = row.GetCell(j).ToString();
                                            break;

                                        case 19:
                                            excel.unit = row.GetCell(j).ToString();
                                            break;

                                        case 20:
                                            excel.MRONO = row.GetCell(j).ToString();
                                            break;

                                         case 21:
                                            excel.MRONAME = "";
                                            break;
                            }




                                }

                            }
                    if (excel!=null && !string.IsNullOrEmpty(excel.DEVICENUMBER)) {
                        list.Add(excel);
                    }
                        
                            Console.WriteLine("\n");
                        }
                    }

            //设备基础信息数据
        //    List<HWEquipmentBasicInformation> hWEquipmentBasics = new List<HWEquipmentBasicInformation>();

            Dictionary<string, HWEquipmentBasicInformation> keyValues = new Dictionary<string, HWEquipmentBasicInformation>();

            //设备详细信息
            List<HWSpecificInformation> hWSpecifics = new List<HWSpecificInformation>();

            //a应该小于 list.count
            for (int a = 0; a < 2; a++)
            {
                EquipmentExcel equipment = list[a];

                List<string> strings = equipment.DEVICENUMBER.Split("\n").ToList();

                //最大值
                List<string> maxList = equipment.Max.Split("\n").ToList();
                //最小值
                List<string> minList = equipment.Min.Split("\n").ToList();


                if (strings.Count > 1)
                {

                    foreach (string s in strings)
                    {
                        HWEquipmentBasicInformation information = new HWEquipmentBasicInformation();
                        information.EQUIPMENTTYPENO = list[a].EQUIPMENTTYPENO;
                        information.DEVICENUMBER = s;
                        information.CREATETIME = date;
                        information.LIFECYCLE = 0;
                        information.ARCHIVESTATE = 0;

                      
                        //hWEquipmentBasics.Add(information);
                        if (!keyValues.ContainsKey(information.EQUIPMENTTYPENO)) {
                            keyValues.Add(information.EQUIPMENTTYPENO, information);
                        }
                    }

                }
                else if (strings.Count == 1)
                {
                    HWEquipmentBasicInformation information = new HWEquipmentBasicInformation();
                    information.EQUIPMENTTYPENO = list[a].EQUIPMENTTYPENO;
                    information.DEVICENUMBER = list[a].DEVICENUMBER;
                    information.CREATETIME = date;
                    information.LIFECYCLE = 0;
                    information.ARCHIVESTATE = 0;

                  
                  //  hWEquipmentBasics.Add(information);
                    if (!keyValues.ContainsKey(information.EQUIPMENTTYPENO))
                    {
                        keyValues.Add(information.EQUIPMENTTYPENO, information);
                    }
                }

                StringBuilder sb = new StringBuilder();

                if (strings.Count > 1) {

                    for (int i = 0; i < strings.Count; i++)
                    {
                        sb.Append(strings[i]);
                        if (i + 1 < strings.Count) {
                            sb.Append(",");
                        }
                    }

                } else if (strings.Count ==1) {

                    sb.Append(list[a].DEVICENUMBER);
                }

                HWSpecificInformation hWSpecificInformation = new HWSpecificInformation();
                hWSpecificInformation.MATERIALNO = list[a].MATERIALNO;
                hWSpecificInformation.DEVICENUMBER = sb.ToString();
                hWSpecificInformation.STOVETEMPERATUREMAX = Convert.ToDouble(maxList[0]);
                hWSpecificInformation.STOVETEMPERATUREMIN = Convert.ToDouble(minList[0]);
                hWSpecificInformation.BEAKTEMPERATUREMAX = Convert.ToDouble(maxList[1]);
                hWSpecificInformation.BEAKTEMPERATUREMIN = Convert.ToDouble(minList[1]);
                hWSpecificInformation.GOOSENECKTEMPERATUREMAX = Convert.ToDouble(maxList[2]);
                hWSpecificInformation.GOOSENECKTEMPERATUREMIN = Convert.ToDouble(minList[2]);
                hWSpecificInformation.EJECTTIMEMAX = Convert.ToDouble(maxList[3]);
                hWSpecificInformation.EJECTTIMEMIN = Convert.ToDouble(minList[3]);
                hWSpecificInformation.COOLINGTIMEMAX = Convert.ToDouble(maxList[4]);
                hWSpecificInformation.COOLINGTIMEMIN = Convert.ToDouble(minList[4]);
                hWSpecificInformation.SPRAYTIMEMAX = Convert.ToDouble(maxList[5]);
                hWSpecificInformation.SPRAYTIMEMIN = Convert.ToDouble(minList[5]);
                hWSpecificInformation.LOCKMOULDFORCE_MAX = Convert.ToDouble(maxList[6]);
                hWSpecificInformation.LOCKMOULDFORCEMIN = Convert.ToDouble(minList[6]);
                hWSpecificInformation.TOTALPRESSUREMAX = Convert.ToDouble(maxList[7]);
                hWSpecificInformation.TOTALPRESSUREMIN = Convert.ToDouble(minList[7]);
                hWSpecificInformation.MRONO = list[a].MRONO;
                hWSpecificInformation.MRONAME = list[a].MRONAME;
                hWSpecificInformation.EQUIPMENTTYPENO = list[a].EQUIPMENTTYPENO;
                hWSpecificInformation.ITEMNAME = list[a].ITEMNAME;
                hWSpecificInformation.EQUIPMENTSPECS = list[a].EQUIPMENTSPECS;
                hWSpecificInformation.CREATETIME = date;

                hWSpecifics.Add(hWSpecificInformation);

            }
            List<HWEquipmentBasicInformation> hWEquipment =  keyValues.Values.ToList();

            Console.WriteLine("\n");
            #region 插入事务
            try
            {
                //开启事务
                _SjDb.Ado.BeginTran();
                int count = await _SjDb.Insertable<HWEquipmentBasicInformation>(hWEquipment[0]).ExecuteCommandAsync();

                int count2 = await _SjDb.Insertable<HWSpecificInformation>(hWSpecifics[0]).ExecuteCommandAsync();

                if (count == 0 && count2==0)
                {
                    throw Oops.Oh($"新建任务失败！");
                }
                //提交事务
                _SjDb.Ado.CommitTran();
               // return "插入成功";
            }
            catch (Exception e)
            {
                //回滚事务
                _SjDb.Ado.RollbackTran();
                throw Oops.Oh($"异常：{e.Message}");
            }
            #endregion

           

        }

                catch (Exception e)
                {
                    //只在Debug模式下才输出
                    Console.WriteLine(e.Message);
                }



        #endregion

        #region
/*        ISheet sheet = null;
        DataTable data = new DataTable();
        int startRow = 0;
        FileStream fs;
        IWorkbook workbook = null;
        int cellCount = 0;//列数
        int rowCount = 0;//行数

        string url = @"E:\excel\2022年生产工单量汇总表(1).xlsx";
        try
        {
            fs = new FileStream(url, FileMode.Open, FileAccess.Read);
            if (url.IndexOf(".xlsx") > 0) // 2007版本
            {
                workbook = new XSSFWorkbook(fs);
            }
            else if (url.IndexOf(".xls") > 0) // 2003版本
            {
                workbook = new HSSFWorkbook(fs);
            }
            if (url != null)
            {
                sheet = workbook.GetSheet("Sheet1 (2)");//根据给定的sheet名称获取数据
            }
            else
            {
                //也可以根据sheet编号来获取数据
                sheet = workbook.GetSheetAt(0);//获取第几个sheet表（此处表示如果没有给定sheet名称，默认是第一个sheet表）  
            }
            if (sheet != null)
            {
                IRow firstRow = sheet.GetRow(0);
                cellCount = firstRow.LastCellNum; //第一行最后一个cell的编号 即总的列数
                if (true)//如果第一行是标题行
                {
                    for (int i = firstRow.FirstCellNum; i < cellCount; ++i)//第一行列数循环
                    {
                        DataColumn column = new DataColumn(firstRow.GetCell(i).StringCellValue);//获取标题
                        data.Columns.Add(column);//添加列
                    }
                    startRow = sheet.FirstRowNum + 1;//1（即第二行，第一行0从开始）
                }
                else
                {
                    startRow = sheet.FirstRowNum;//0
                }
                //最后一行的标号
                rowCount = sheet.LastRowNum;
                for (int i = startRow; i <= rowCount; ++i)//循环遍历所有行
                {
                    IRow row = sheet.GetRow(i);//第几行
                    if (row == null)
                    {
                        continue; //没有数据的行默认是null;
                    }
                    //将excel表每一行的数据添加到datatable的行中
                    DataRow dataRow = data.NewRow();
                    for (int j = row.FirstCellNum; j < cellCount; ++j)
                    {
                        if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
                        {
                            dataRow[j] = row.GetCell(j).ToString();
                        }
                    }
                    data.Rows.Add(dataRow);
                }
            }
        
        }
        catch (Exception ex)
        {
            Console.WriteLine("Exception: " + ex.Message);
       
        }*/

        #endregion
    }

} 
